package com.basicsys.modules.util;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ExcelUtil {
	// 将数据导出到Excel
	public static void excelOut(List<Book> list, String path) {
		WritableWorkbook bWorkbook = null;
		try {
			// 创建Excel对象
			bWorkbook = Workbook.createWorkbook(new File(path));
			// 通过Excel对象创建一个选项卡对象
			WritableSheet sheet = bWorkbook.createSheet("sheet1", 0);
			// 使用循环将数据读出
			for (int i = 0; i < list.size(); i++) {
				Book book = list.get(i);
				Label label = new Label(0, i, String.valueOf(book.getId()));
				Label label1 = new Label(1, i, String.valueOf(book.getName()));
				Label label2 = new Label(2, i, String.valueOf(book.getType()));
				sheet.addCell(label);
				sheet.addCell(label1);
				sheet.addCell(label2);
			}

			// 创建一个单元格对象，第一个为列，第二个为行，第三个为值
			Label label = new Label(0, 2, "test");
			// 将创建好的单元格放入选项卡中
			// sheet.addCell(label);
			// 写如目标路径
			bWorkbook.write();

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				bWorkbook.close();
			} catch (WriteException | IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

	}

	public static List<Book> ExcelIn(String path) {
		List<Book> arrayList = new ArrayList<Book>();
		Workbook bWorkbook = null;
		try {
			bWorkbook = Workbook.getWorkbook(new File(path));
			Sheet sheet = bWorkbook.getSheet(0);
			for (int i = 0; i < sheet.getRows(); i++) {
				Book book = new Book();
				// 获取单元格对象
				Cell cell = sheet.getCell(0, i);
				// 获取单元格的值
				book.setId(cell.getContents());
				book.setName(sheet.getCell(1, i).getContents());
				book.setType(sheet.getCell(2, i).getContents());
				arrayList.add(book);

			}

		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			bWorkbook.close();
		}
		return arrayList;
	}

}
